/* Aula 05 */
CREATE DATABASE AULA05
GO

USE AULA05 
GO

CREATE TABLE CLIENTE(
	IDCLIENTE		INT			IDENTITY,
	NOME			VARCHAR(50)	NOT NULL,
	EMAIL			VARCHAR(50)	UNIQUE
)
GO

ALTER TABLE CLIENTE ADD CONSTRAINT PK_CLIENTE_IDCLIENTE PRIMARY KEY(IDCLIENTE)
GO

INSERT INTO CLIENTE VALUES ('JOAO','JOAO@GMAIL.COM')
INSERT INTO CLIENTE VALUES ('JOSE','JOSE@GMAIL.COM')
INSERT INTO CLIENTE VALUES ('COTI','ADM@COTI.COM.BR')
INSERT INTO CLIENTE VALUES ('MICROSOFT','ADM@MSFT.COM')
INSERT INTO CLIENTE VALUES ('VICTOR','VICTOR@IG.COM')
INSERT INTO CLIENTE VALUES ('AMARAL','AMARAL@IG.COM')
INSERT INTO CLIENTE VALUES ('STUART','LITTLE@GMAIL.COM')
INSERT INTO CLIENTE VALUES ('GABRIEL','GABRIEL@IG.COM')
INSERT INTO CLIENTE VALUES ('LEONARDO','LEONARDO@GMAIL.COM')
GO

CREATE TABLE ENDERECO(
	IDENDERECO	INT			IDENTITY,
	BAIRRO		VARCHAR(30)	NOT NULL,
	UF			CHAR(2),
	ID_CLIENTE	INT			UNIQUE
)
GO

ALTER TABLE ENDERECO ADD CONSTRAINT PK_ENDIDEND PRIMARY KEY(IDENDERECO)
ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDCLI	FOREIGN KEY(ID_CLIENTE)
	REFERENCES CLIENTE(IDCLIENTE)
	ON DELETE CASCADE
GO

INSERT INTO ENDERECO VALUES('BOTAFOGO'	,'RJ',1)
INSERT INTO ENDERECO VALUES('FLAMENGO'	,'RJ',2)
INSERT INTO ENDERECO VALUES('CENTRO'	,'SP',3)
INSERT INTO ENDERECO VALUES('PELOTAS'	,'RS',6)
INSERT INTO ENDERECO VALUES('CAMPINAS'	,'SP',7)
INSERT INTO ENDERECO VALUES('FLAMENGO'	,'RJ',4)
INSERT INTO ENDERECO VALUES('CENTRO'	,'RJ',5)
INSERT INTO ENDERECO VALUES('CENTRO'	,'MG',8)

CREATE TABLE TELEFONE(
	IDTELEFONE		INT	IDENTITY,
	TIPO			CHAR(3),
	NUMERO			CHAR(10),
	ID_CLIENTE		INT
)
GO

ALTER TABLE TELEFONE ADD CONSTRAINT PK_TEL_IDTEL
	PRIMARY KEY(IDTELEFONE)
ALTER TABLE TELEFONE ADD CONSTRAINT FK_TEL_IDCLI
	FOREIGN KEY(ID_CLIENTE)
	REFERENCES CLIENTE(IDCLIENTE)
ON DELETE SET NULL

ALTER TABLE TELEFONE ADD CONSTRAINT CK_TEL_TIPO
	CHECK (TIPO IN ('CEL','RES','COM'))
GO

INSERT INTO TELEFONE VALUES ('COM','22230822',6)
INSERT INTO TELEFONE VALUES ('CEL','22844104',3)
INSERT INTO TELEFONE VALUES ('COM','22500678',3)
INSERT INTO TELEFONE VALUES ('RES','23661423',5)
INSERT INTO TELEFONE VALUES ('COM','23073686',1)
INSERT INTO TELEFONE VALUES ('CEL','24441929',4)
INSERT INTO TELEFONE VALUES ('RES','24074817',4)
INSERT INTO TELEFONE VALUES ('COM','22629604',5)
INSERT INTO TELEFONE VALUES ('CEL','22685854',5)
INSERT INTO TELEFONE VALUES ('COM','22885832',5)
INSERT INTO TELEFONE VALUES ('RES','22949761',4)
GO

CREATE TABLE PF (
	NASCIMENTO	DATE	NOT NULL,
	SEXO		CHAR(1)	NOT NULL,
	ID_CLIENTE	INT		NOT NULL UNIQUE
)
GO

ALTER TABLE PF ADD CONSTRAINT CK_SEXO
	CHECK(SEXO IN ('M','F'))
	GO

ALTER TABLE PF ADD CONSTRAINT PF_PK
PRIMARY KEY(ID_CLIENTE)
GO

ALTER TABLE PF ADD CONSTRAINT FK_PF_CLIENTE
	FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE)
	ON DELETE CASCADE
GO

INSERT INTO PF VALUES('07/02/1981','M',1)
INSERT INTO PF VALUES('10/03/1985','M',2)
INSERT INTO PF VALUES('02/11/1975','M',5)
INSERT INTO PF VALUES('08/12/1975','M',6)
INSERT INTO PF VALUES('12/15/1974','M',7)
INSERT INTO PF VALUES('10/09/1986','M',8)
GO

/* CRIANDO TABELAS E CHAVES COMPOSTAS ENVOLVIDAS */
CREATE TABLE PJ(
	RAMO		VARCHAR(30) NOT NULL,
	ID_CLIENTE	INT	UNIQUE	NOT NULL
)
GO

/* CHAVE COMPOSTA */
ALTER TABLE PJ ADD CONSTRAINT PK_PJ_CLIENTE
	PRIMARY KEY(ID_CLIENTE,RAMO)
	GO

ALTER TABLE PJ ADD CONSTRAINT FK_PJ_CLIENTE
	FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE)
	ON DELETE CASCADE
GO

INSERT INTO PJ VALUES ('ACADEMICO',3)
INSERT INTO PJ VALUES ('TI',4)

/* NOTA FISCAL */
/* USA COLCHETES PARA NOME COMPOSTO 
DE TABELA*/
CREATE TABLE [NOTA FISCAL](
	IDNOTA		INT		IDENTITY(1000,1),
	ID_CLIENTE	INT,
	DATA		DATE
)

ALTER TABLE [NOTA FISCAL] ADD CONSTRAINT PK_NF_IDNF PRIMARY KEY(IDNOTA)
ALTER TABLE [NOTA FISCAL] ADD CONSTRAINT FK_NOTA_CLIENTE
	FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE)
	ON DELETE SET NULL
GO

CREATE TABLE [ITEM NOTA](
	IDITEMNOTA		INT		IDENTITY,
	ID_NOTA			INT,
	ID_PRODUTO		INT,
	QUANTIDADE		INT
)

ALTER TABLE [ITEM NOTA] ADD CONSTRAINT PK_IN_IDIN PRIMARY KEY(IDITEMNOTA)
GO

CREATE TABLE PRODUTO(
	IDPRODUTO	INT	IDENTITY,
	NOME		VARCHAR(50)		NOT NULL,
	CATEGORIA	VARCHAR(20)		NOT NULL,
	PRECO		NUMERIC(10,2)	NOT NULL
)
GO

ALTER TABLE PRODUTO ADD CONSTRAINT PK_PRODUTO PRIMARY KEY(IDPRODUTO)
GO

ALTER TABLE [ITEM NOTA] ADD CONSTRAINT FK_PRODUTO_IN_ID
	FOREIGN KEY(ID_PRODUTO) REFERENCES PRODUTO(IDPRODUTO)
GO

INSERT INTO PRODUTO VALUES ('TV 3D 32" SAMSUNG','TVS',1600.00)
INSERT INTO PRODUTO VALUES ('LIVRO JAVA 6','LIVROS',95.00)
INSERT INTO PRODUTO VALUES ('CAMERA DIGITAL CANNON','CAMERAS',963.00)
INSERT INTO PRODUTO VALUES ('SMARTPHONE SAMSUNG I9300','CELULARES',1300.00)
INSERT INTO PRODUTO VALUES ('HD EXTERNO SAMSUNG','HDS',350.00)
GO

SELECT * FROM [NOTA FISCAL] 
GO

INSERT INTO [NOTA FISCAL] (ID_CLIENTE,DATA) VALUES (1,'01/05/2012')
GO

SELECT * FROM [NOTA FISCAL] 
GO

INSERT INTO [ITEM NOTA] VALUES (1000,1,2)
INSERT INTO [ITEM NOTA] VALUES (1000,2,1)
INSERT INTO [ITEM NOTA] VALUES (1000,4,1)
GO

/* CRIANDO VIEWS */
CREATE VIEW TOTAL_NOTA AS
	SELECT IDNOTA,DATA,NOME,QUANTIDADE, PRECO, (QUANTIDADE * PRECO) AS TOTAL
	FROM [NOTA FISCAL] N INNER JOIN [ITEM NOTA] IT ON N.IDNOTA = IT.ID_NOTA
	INNER JOIN PRODUTO P
	ON IDPRODUTO = ID_PRODUTO
	GO
	
SELECT * FROM TOTAL_NOTA
GO

SELECT IDNOTA, SUM(TOTAL) AS TOTAL_NOTA
FROM TOTAL_NOTA
GROUP BY IDNOTA
GO

CREATE VIEW DETALHE_NOTA AS 
	SELECT IDNOTA, SUM(TOTAL) AS TOTAL_NOTA
	FROM TOTAL_NOTA
	GROUP BY IDNOTA
GO

SELECT * FROM DETALHE_NOTA
GO

/* NOTAS E CLIENTES */
CREATE VIEW NOTA_CLIENTE 
AS
	SELECT IDNOTA, DATA, C.NOME CLIENTE, P.NOME PRODUTO,
	QUANTIDADE, PRECO, (QUANTIDADE * PRECO) SUBTOTAL
	FROM [NOTA FISCAL] N INNER JOIN [ITEM NOTA] IT 
	ON N.IDNOTA = IT.ID_NOTA
	INNER JOIN PRODUTO P
	ON P.IDPRODUTO = IT.ID_PRODUTO
	INNER JOIN CLIENTE C
	ON IDCLIENTE = ID_CLIENTE
	GO

SELECT * FROM NOTA_CLIENTE
GO

INSERT INTO [NOTA FISCAL] VALUES (2,'02/05/2012')
INSERT INTO [NOTA FISCAL] VALUES (3,'10/06/2012')
INSERT INTO [NOTA FISCAL] VALUES (4,'06/15/2012')
INSERT INTO [NOTA FISCAL] VALUES (5,'08/25/2012')
INSERT INTO [NOTA FISCAL] VALUES (6,'09/05/2012')
GO

INSERT INTO [ITEM NOTA] VALUES (1001,2,3)
INSERT INTO [ITEM NOTA] VALUES (1001,5,3)
INSERT INTO [ITEM NOTA] VALUES (1002,3,1)
INSERT INTO [ITEM NOTA] VALUES (1003,2,1)
INSERT INTO [ITEM NOTA] VALUES (1003,6,4)
INSERT INTO [ITEM NOTA] VALUES (1004,4,2)
INSERT INTO [ITEM NOTA] VALUES (1004,2,1)
INSERT INTO [ITEM NOTA] VALUES (1005,1,1)
INSERT INTO [ITEM NOTA] VALUES (1005,3,3)
INSERT INTO [ITEM NOTA] VALUES (1005,5,2)
GO

SELECT * FROM NOTA_CLIENTE
GO

SELECT CLIENTE, SUM(SUBTOTAL) TOTAL
FROM NOTA_CLIENTE
GROUP BY CLIENTE
ORDER BY 2 DESC
GO

/* TOTAL DE NOTAS DE CLIENTES */
SELECT CLIENTE,DATA, SUM(SUBTOTAL) TOTAL
FROM NOTA_CLIENTE
GROUP BY CLIENTE,DATA
GO

/* CRIE VIEW PARA VERIFICAR OS ITENS MAIS VENDIDOS */
CREATE VIEW PRODUTOS_VENDIDOS AS
 SELECT PRODUTO, SUM(QUANTIDADE) QUANTIDADE
 FROM NOTA_CLIENTE
 GROUP BY PRODUTO
GO 

SELECT * FROM PRODUTOS_VENDIDOS
GO

/* PROCEDURE DE CADASTRO DE PRODUTOS */
CREATE PROCEDURE SP_COMPRA
	@CLIENTE	INT,
	@PRODUTO	INT,
	@QUANTIDADE	INT,
	@DATA		DATE
AS
	DECLARE @NOTA INT
	INSERT INTO [NOTA FISCAL] VALUES (@CLIENTE,@DATA)
	
	SET @NOTA = (SELECT IDNOTA FROM [NOTA FISCAL] WHERE IDNOTA = @@IDENTITY)
	/*@@IDENTITY SIGNIFICA O ULTIMO ID INSERIDO*/
	
	INSERT INTO [ITEM NOTA] VALUES (@NOTA,@PRODUTO, @QUANTIDADE)
	GO
	
	EXEC SP_COMPRA 1,2,1,'01/02/2012'
	GO
	
	/**/
	SELECT * FROM TOTAL_NOTA
	SELECT * FROM DETALHE_NOTA
	SELECT * FROM NOTA_CLIENTE
	GO
	
	/* XML */
	/*	@ VIRA ATRIBUTO */
	/* SEM @ VIRA TAG*/
	SELECT 
	IDCLIENTE	AS "CLIENTE/@ID",
	NOME		AS "CLIENTE/NOME",
	EMAIL		AS "CLIENTE/EMAIL",
	TIPO		AS "CLIENTE/CONTATO/TIPO",
	NUMERO		AS "CLIENTE/CONTATO/NUMERO"
	FROM CLIENTE C INNER JOIN TELEFONE T
	ON IDCLIENTE = ID_CLIENTE
	FOR XML PATH('CLIENTE'),
	ROOT('COTIINFORMATICA')
	GO

SELECT
CLIENTE AS "CLIENTE",
DATA AS "DATA",
SUM(SUBTOTAL) AS TOTAL
FROM NOTA_CLIENTE
GROUP BY CLIENTE,DATA
FOR XML PATH('CLIENTE'),
ROOT('COTIINFORMATICA')
GO